use crate::database::db_conn;
use crate::CustomResult::*;
use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow; // 数据库连接

/************************** 实体  ***************************/

// FromRow 使得 User 可以成为sqlx查询的row 对应的类型
#[derive(Debug, FromRow, Serialize, Deserialize, Default, Clone)]
pub struct User {
    pub id: i32,
    pub name: Option<String>,
    pub password: Option<String>,
    pub gender: Option<i32>,
    pub email: Option<String>,
    pub remark: Option<String>,
    pub token: Option<String>,
    pub open_id: Option<String>, //微信唯一用户凭证
    pub created_time: Option<DateTime<Utc>>, // sqlx 子包 中需添加 feature=chrono 才能将时间 作为字段
    pub updated_time: Option<DateTime<Utc>>,
}

/************************** 插入  ***************************/
pub async fn insert (
    name: &str,
    password: &str,
    gender: i32,
    email: &str,
    remark: &str,
) -> CustomResult<u64> {
    // 连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    // 执行插入操作
    let res = sqlx::query!(
        "INSERT INTO t_user (name, password, gender, email, remark) VALUES (?, ?, ?, ?, ?)",
        name,
        password,
        gender,
        email,
        remark
    )
    .execute(&pool)
    .await
    .map_err(CustomError::from)?
    .last_insert_id();

    Ok(res)
}

/************************** 更新  ***************************/
pub async fn update(
    id: i32,
    name: &str,
    password: &str,
    gender: i32,
    email: &str,
    remark: &str,
) -> CustomResult<User> {
    // 连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    //更新user
    let _ = sqlx::query(
        "UPDATE t_user SET name = ?, password = ?, gender = ?, email = ?, remark = ? WHERE id = ?",
    )
    .bind(name)
    .bind(password)
    .bind(gender)
    .bind(email)
    .bind(remark)
    .bind(id)
    .execute(&pool)
    .await
    .map_err(CustomError::from);

    // 查询user
    let user = sqlx::query_as::<_, User>("SELECT * FROM t_user WHERE id = ?")
        .bind(id)
        .fetch_one(&pool)
        .await?;

    Ok(user)
}

/************************** 删除  ***************************/
pub async fn delete(id: i32) -> CustomResult<()> {
    // 连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(|e| CustomError::SqlError(e.into()))?;

    //更新user
    let res = sqlx::query("DELETE FROM t_user WHERE id = ?")
        .bind(id)
        .execute(&pool)
        .await
        .map_err(CustomError::from)?;
    if res.rows_affected() == 0 {
        return Err("删除失败".into());
    }
    Ok(())
}

/************************** 查询列表  ***************************/
pub async fn list() -> CustomResult<Vec<User>> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    //全部查询
    let res = sqlx::query_as::<_, User>("SELECT * FROM t_user ORDER BY id ASC")
        .fetch_all(&pool)
        .await
        .map_err(CustomError::from);

    res
}

/************************** 查询分页  ***************************/
pub async fn pages(page: u32, page_size: u32) -> CustomResult<Vec<User>> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    // 计算偏移量
    let offset = (page - 1) * page_size;

    // 分页查询
    let res = sqlx::query_as::<_, User>("SELECT * FROM t_user ORDER BY id ASC LIMIT ? OFFSET ?")
        .bind(page_size)
        .bind(offset)
        .fetch_all(&pool)
        .await
        .map_err(Into::into);

    res
}

/************************** 查询详情 by id  ***************************/
pub async fn find_by_id(id: i32) -> CustomResult<User> {

    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    // 根据id 查询
    let users = sqlx::query_as::<_, User>("SELECT * FROM t_user WHERE id = ?")
        .bind(id)
        .fetch_all(&pool)
        .await
        .map_err(CustomError::from)?;

    let user = users.into_iter().find(|user| user.id == id).clone();

    user.ok_or_else(||CustomError::TipError("不存在的user_id".to_string()))
}

/************************** 查询详情 by name  ***************************/
pub async fn find_by_name(name: &str) -> CustomResult<Vec<User>> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(|e| CustomError::SqlError(e))?;

    // 根据name 查询
    let res = sqlx::query_as::<_, User>("SELECT * FROM t_user WHERE name = ?")
        .bind(name)
        .fetch_all(&pool)
        .await
        .map_err(CustomError::from)?;


    Ok(res)
}

/************************** 查询详情 by open_id  ***************************/
pub async fn find_by_open_id(open_id: &str) -> CustomResult<Vec<User>> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(|e| CustomError::SqlError(e))?;

    // 根据open_id 查询
    let res = sqlx::query_as::<_, User>("SELECT * FROM t_user WHERE open_id = ?")
        .bind(open_id)
        .fetch_all(&pool)
        .await
        .map_err(CustomError::from)?;

    Ok(res)
}

/************************** 更新 token  ***************************/
pub async fn upsert_token_by_id(id: i32, token: &str) -> CustomResult<()> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    // 根据userId 更新token
    let update_result = sqlx::query("UPDATE t_user SET token = ? WHERE id = ?")
        .bind(token)
        .bind(id)
        .execute(&pool)
        .await
        .map_err(CustomError::from)?;

    // 检查更新的行数
    if update_result.rows_affected() == 0 {
         Err(CustomError::TipError("更新token失败".to_string()))
    } else {
        Ok(())
    }
}

/************************** 更新 用户的 open_id  ***************************/
pub async fn upsert_open_id_by_id(id: i32, open_id: &str) -> CustomResult<()> {
    //连接到数据库
    let pool = db_conn::get_pool()
        .await
        .map_err(CustomError::from)?;

    // 根据userId 更新open_id
    let update_result = sqlx::query("UPDATE t_user SET open_id = ? WHERE id = ?")
        .bind(open_id)
        .bind(id)
        .execute(&pool)
        .await
        .map_err(CustomError::from)?;

    // 检查更新的行数
    if update_result.rows_affected() == 0 {
         Err(CustomError::TipError("更新open_id失败".to_string()))
    } else {
        Ok(())
    }
}
